#!/usr/bin/perl
#

use strict;
use warnings;
use DBI;
my $fh;
my $logfile="/var/log/opensips.log";

open $fh,"tail -f $logfile |" or die("$!");

# connect to database
my $dbh=DBI->connect("DBI:mysql:database=data_collection;host=localhost","opensips","opensipsrw.",{'RaiseError'=>1});
my ($cols,$vals,$acc,$ip,$port,$sql);
#my %a=("dtype","device type", "dversion", "device version","browser","browser version");
#my %b=("file","file type","soft","favorite software","url","favorite web");
my %a;

while (chomp(my $line=<$fh>)){
    $cols="";
    $vals="";

    # REGISTER sip:192.168.1.252 from udp:192.168.1.241:1212, sip:aaa_11@1111.1.1 -> sip:aaa_11@111.1.1.1
    if($line=~/.*:\s+([A-Z]+)\s+sip:.*from\s+\w+:([\d\.]+):([\d]+),\s+sip:([\w_\-]+)\@[\d\.]+.*->\s+sip:([\w_\-]+)\@[\d\.]+.*/){
        my $method="$1";
        $acc="$4";
        $ip="$2";
        $port="$3";
        my $to="$5";

        if ( $method eq "REGISTER"){
            $sql="select * from `login` where account = '$acc' and ip='$ip' and  port='$port'";
            my $sth=$dbh->prepare($sql);
            $sth->execute;
            my $num_rows=$sth->rows;
            $sth->finish;
            if($num_rows == 0){
                $sql="insert into `login` (account, ip, port, start_time, times) values('$acc', '$ip','$port',now(),0)" ;
                $dbh->do($sql);
            }else{
                $sql="update `login` set times = times + 1, last_time = now() where account='$acc'  and ip = '$ip' and port = '$port'";
                $dbh->do($sql);
            }
            #print $sql;
        }elsif($method eq "INVITE"){
            my $sth=$dbh->prepare("select * from `call` where from_ip='$ip:$port' and call_from = '$acc' and call_to='$to' and status = 1");
            $sth->execute;
            my $num_r=$sth->rows;
            $sth->finish;
            if($num_r == 0){
                $sql="insert into `call`(from_ip, call_from, call_to, status, start_time) values('$ip:$port','$acc', '$to', 1, now())";
                $dbh->do($sql);
            }
            #print $sql;
        }elsif($method eq "BYE"){
            my $sth=$dbh->prepare("select * from `call` where  status = 1 and (call_from = '$acc' and call_to='$to') or (call_from = '$to' and call_to = '$acc')");
            $sth->execute;
            my $num_r=$sth->rows;
            $sth->finish;
            if($num_r > 0){
                $sql="update `call` set end_time = now(), status = 0 where status = 1 and (call_from = '$acc' and call_to='$to') or (call_from = '$to' and call_to = '$acc')";
                $dbh->do($sql);
            }
        }
        next;
    }
    %a=("nat type", "nattype", "device type", "device", "device version","version");
    $cols="";
    $vals="";
    my $q="";
    while(my($t,$t1) = each %a){
        if($line=~/.*:\s+([\d\.]+:[\d]+)\s+.*$t:\s*([^,]+)[,]{0,1}/){
            $ip="$1";
            $cols.=",$t1";
            $vals.=",'$2'";
            $q.= "and $t1='$2'"
        }
    }
    if($cols and $vals){
        my $sth=$dbh->prepare("select * from `devinfo` where  ip = '$ip' $q");
        $sth->execute;
        my $num_r=$sth->rows;
        $sth->finish;
        if($num_r == 0){
            $sql="insert into devinfo(ip $cols) values('$ip' $vals)";
            #print $sql;
            $dbh->do($sql);
        }
        next;
    }
    if($line=~/.*:\s+([\d\.]+:[\d]+)\s+browser:\s+(.*)/){
        my $sth=$dbh->prepare("select * from `client` where ip='$1' and browser = '$2'");
        $sth->execute;
        my $num_r=$sth->rows;
        $sth->finish;
        if($num_r == 0){
            $sql="insert into `client`(ip,browser) values('$1','$2')";
            #print $sql;
            $dbh->do($sql);
        }
        next;
    }
    if($line=~/.*:\s+([\d\.]+:[\d]+)\s+favorite\s+(\w+):\s+(.*)/){
        my $sth=$dbh->prepare("select * from `favorite` where ip='$1' and favorite='$3' and type = '$2'");
        $sth->execute;
        my $num_r=$sth->rows;
        if($num_r == 0){
            $sql="insert into favorite(ip,favorite,type) values('$1','$3','$2')";
            #print $sql;
            $dbh->do($sql);
        }
    }
}
$dbh->disconnect;
